/* ExcelBalanceToDB.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2008-2-23	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.imp;

import java.io.DataInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import system.base.Balance;
import system.base.Balance;
import system.base.ServerDownupfileInfo;
import framework.core.ConstDefine;
import framework.core.DBConnection;
import framework.core.ILoginContext;
import framework.core.LogWriter;


public class ExcelBalanceToDB implements GWBaseProcesser {
    public static String doSaveToDB(DataInputStream aFile, DBConnection con, ILoginContext loginContext, String tableName, String warehouseCode,String upDownType)
    throws GWException
 {
 int tSuccCount = 0;
 int tFailCount = 0;
 String tFailFile = "0";
 HSSFWorkbook tHSSFWorkbook = new HSSFWorkbook();
 /*int number = tHSSFWorkbook.getNumberOfNames();
 for(int i = 0;i<number;i++){
 tableName = tHSSFWorkbook.getSheetAt(i).toString();
 }
 */
 //开始读取excel文件
 HSSFSheet tHSSFSheet = null;
 try {
    tHSSFWorkbook = new HSSFWorkbook(aFile);
    tHSSFSheet = tHSSFWorkbook.getSheet(tableName);
    
 } catch (IOException e) {
    e.getStackTrace();
    new Exception("批量下载文件失败");
 }
 ArrayList alist = new ArrayList();
 ArrayList tsucceslist = new ArrayList();
 ArrayList tErrList = new ArrayList();

 //读取并检查Excel明细
 int tRowCount = parseExcelBalance(alist, tHSSFSheet, loginContext);
 //int tRowCount = parseExcelBalance(alist,tHSSFSheet);
 if (alist.size() > 0) {
    // 保存上传文件到数据库
    for (int i = 0; i < alist.size(); i++) {
        try {
            GWManufactureUploadVO gwManufactureUpload = (GWManufactureUploadVO) alist.get(i);

            Balance bal = gwManufactureUpload.getBalance();

            // 判断数据库中是否存在该条记录
            // 根据仓库编码和分类编码得到是否在数据库中存在该条信息
            /*
             * Conditions cs = new Conditions();
             * cs.add(WarehouseCategory.WAREHOUSE_CODE,wc.getWarehouseCode());
             * cs.add(WarehouseCategory.CATEGORY_CODE,wc.getCategoryCode());
             * WarehouseCategory warehouseCategory =
             * WarehouseCategory.fetch(con,cs);
             */

            Balance balance = Balance.fetch(con, bal.getId());
            if (bal == null) {
                // 数据库中不存在的操作:得到ID = GUID值
                balance = Balance.fetch();
            }

            // 更新当前一条Excel信息数据库
        
            balance.setBalanceBillCode(bal.getBalanceBillCode());
            balance.setBillType(bal.getBillType());
            balance.setBillId(bal.getBillId());
            balance.setBillCode(bal.getBillCode());
            balance.setTotalMoney(bal.getTotalMoney());
            balance.setMoney(bal.getMoney());
            bal.setPayType(bal.getPayType());
            balance.setBalanceFlag(bal.getBalanceFlag());
            balance.setBalanceUserId(bal.getBalanceUserId());
            balance.setBalanceTime(bal.getBalanceTime());
            balance.setBalanceUser(bal.getBalanceUser());
            balance.setStatus(bal.getStatus());
            balance.setCreateUserId(bal.getCreateUserId());
            balance.setCreateUser(bal.getCreateUser());
            balance.setCreateTime(bal.getCreateTime());
            balance.setLastUpdateUserId(bal.getLastUpdateUserId());
            balance.setLastUpdateUser(bal.getLastUpdateUser());
            balance.setLastUpdateTime(bal.getLastUpdateTime());
            balance.setIsSync(bal.getIsSync());
            balance.save(con);
            tSuccCount++;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            tFailCount++;
        }
    }
                
    // 在循环结束后在客户上传下载表中记录操作信息
    try {
        ServerDownupfileInfo sdfi = ServerDownupfileInfo.fetch();
        sdfi.setCreateTime(new framework.core.DateUtil().getFullString());
        sdfi.setCreateUser(ConstDefine.EXCELFILE_CREATE_USER_AUTO);
        sdfi.setCreateUserId(ConstDefine.EXCELFILE_CREATE_USER_ID_AUTO);
        sdfi.setDownloadtyp(upDownType);
        sdfi.setLastUpdateTime(new framework.core.DateUtil().getDateString());
        sdfi.setLastUpdateUser(loginContext.getLoginUserName());
        sdfi.setLastUpdateUserId(loginContext.getLoginUserId());
        sdfi.setParmsstr(tableName);
        sdfi.setDatatableName(tableName);
        sdfi.setParmstype(ServerDownupfileInfo.UPLOAD_PARMSTYPE_TEXT);
        sdfi.setStatus(ConstDefine.STR_YES);
        sdfi.setUpdownState(ServerDownupfileInfo.UPLOAD_STATE_SUCCESS);
        sdfi.setWarehouseUincode(warehouseCode);
        sdfi.save(con);
    } catch (Exception e) {
        System.out.println(e.getMessage());    
    }

 }
 //读取sheet表单
 return tSuccCount + "-" + tFailCount + "-" + tRowCount;
 }

 //end chengbiao.he

 /* start chengbiao.he */
 //解析Excel文件
 private static int parseExcelBalance(ArrayList aList, HSSFSheet aHSSFSheet, ILoginContext loginContext) throws GWException {
 // private static int parseExcelBalance(ArrayList aList,HSSFSheet aHSSFSheet)throws GWException {
 int i = 0;
 //取得第一行的对象
 HSSFRow tFirstRow = aHSSFSheet.getRow(0);

 DecimalFormat a = new DecimalFormat("#");
 while (true) {
    HSSFRow tHSSFRow = aHSSFSheet.getRow(1 + i);
    boolean flag = false;
    try {

        for (int j = 0; j < 20; j++) {
            if (tHSSFRow == null) {
                break;
            }
            if (tHSSFRow.getCell((short) j) == null) {
                break;
            }
            flag = true;
            break;
        }
        if (!flag) {
            break;
        } else {
            GWManufactureUploadVO tGSManufactureUploadVO = new GWManufactureUploadVO();
            int tResult = 1;
            String tFailReason = "";

            Balance bal = new Balance();

            for (int j = 0; j <20; j++) {
                // 获得EXCEL首行记录
                String tFiledName = tFirstRow.getCell((short) j).getStringCellValue();
                // 序号
                if (tFiledName.equalsIgnoreCase(Balance.ID)) {
                    String tID = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                    bal.setId(tID);
                }
                // 入库单编码
                else if (tFiledName.equalsIgnoreCase(Balance.BALANCE_BILL_CODE)) {
                    String tBalanceBillCode = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                    bal.setBalanceBillCode(tBalanceBillCode);
                }
                // 仓库编码
                else if (tFiledName.equalsIgnoreCase(Balance.BILL_TYPE)) {
                    String tBillType = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                    bal.setBillType(tBillType);
                }
                // 仓库名称
                else if (tFiledName.equalsIgnoreCase(Balance.BILL_ID)) {
                    //int tWarehouseName = tHSSFRow.getCell((short)j).getCellType();
                    String tBillId = PoiUtil.getCellValue(tHSSFRow, j, "");
                    bal.setBillId(tBillId);

                }
                // 供应商编码
                else if (tFiledName.equalsIgnoreCase(Balance.BILL_CODE)) {
                    String tBillCode = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                    bal.setBillCode(tBillCode);
                }
                // 供应商名称
                else if (tFiledName.equalsIgnoreCase(Balance.TOTAL_MONEY)) {
                    //String tTotalMoney = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue().trim();
                    double tTotalMoney = tHSSFRow.getCell((short)j).getNumericCellValue();
                    bal.setTotalMoney(tTotalMoney);

                }
                // 对方仓库编码
                else if (tFiledName.equalsIgnoreCase(Balance.MONEY)) {
                    //String tMoney = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue();
                    double tMoney = tHSSFRow.getCell((short)j).getNumericCellValue();
                    bal.setMoney(tMoney);
                }

                // 对方仓库名称
                else if (tFiledName.equalsIgnoreCase(Balance.PAY_TYPE)) {
                    String tPayType = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getNumericCellValue());
                    bal.setPayType(tPayType);
                }
                // 入库时间
                else if (tFiledName.equalsIgnoreCase(Balance.BALANCE_FLAG)) {
                    String tBalanceFlag = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setBalanceFlag(tBalanceFlag);
                }
                // 入库类型
                else if (tFiledName.equalsIgnoreCase(Balance.BALANCE_USER_ID)) {
                    String tBalanceUserId = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setBalanceUserId(tBalanceUserId);
                }
                // 入库金额
                else if (tFiledName.equalsIgnoreCase(Balance.BALANCE_USER)) {
                     // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                    String tBalanceUser = PoiUtil.getCellValue(tHSSFRow, j, "");
                    bal.setBalanceUser(tBalanceUser);
                }
                // 送货单号
                else if (tFiledName.equalsIgnoreCase(Balance.BALANCE_TIME)) {
                    String tBalanceTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setBalanceTime(tBalanceTime);
                }
                // 备注
                if (tFiledName.equalsIgnoreCase(Balance.STATUS)) {
                    String tStatus = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                    bal.setStatus(tStatus);
                }
               
               
                // 创建用户ID
                else if (tFiledName.equalsIgnoreCase(Balance.CREATE_USER_ID)) {
                    String tCreateUserId = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setCreateUserId(tCreateUserId);
                }

                // 创建用户
                else if (tFiledName.equalsIgnoreCase(Balance.CREATE_USER)) {
                    String tCreateUser = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getNumericCellValue());
                    bal.setCreateUser(tCreateUser);
                }
                // 单证创建时间
                else if (tFiledName.equalsIgnoreCase(Balance.CREATE_TIME)) {
                    String tCreateTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setCreateTime(tCreateTime);
                }
                // 最后修改人序号
                else if (tFiledName.equalsIgnoreCase(Balance.LAST_UPDATE_USER_ID)) {
                    String tLastUpdateUserId = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setLastUpdateUserId(tLastUpdateUserId);
                }
                // 最后修改人
                else if (tFiledName.equalsIgnoreCase(Balance.LAST_UPDATE_USER)) {
                    String tLastUpdateUser = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                    bal.setLastUpdateUser(tLastUpdateUser);
                }
                // 最后修改时间
                else if (tFiledName.equalsIgnoreCase(Balance.LAST_UPDATE_TIME)) {
                    String tLastUpdateTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setLastUpdateTime(tLastUpdateTime);
                }
             // 是否已同步
                else if (tFiledName.equalsIgnoreCase(Balance.IS_SYNC)) {
                    String IsSync = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                    bal.setIsSync(IsSync);
                }
                

            }
            tGSManufactureUploadVO.setBalance(bal);
            // tGSManufactureUploadVO.setFailure_reason(tFailReason);
            tGSManufactureUploadVO.setInsert_result(String.valueOf(tResult));
            tGSManufactureUploadVO.setRowNumber(i + 1);
            aList.add(tGSManufactureUploadVO);
            i++;
        }
    } catch (RuntimeException e) {
        LogWriter.error("****Manufacture BatchImport RuntimeEx :[" + e.getMessage() + "]");
        throw new GWException("导入数据中某一列数据类型不符合规定.");
    } catch (Error e) {
        LogWriter.error("****Manufacture BatchImport ErroeEx :[" + e.getMessage() + "]");
        throw new GWException(e.getMessage());
    } catch (Exception e) {
        LogWriter.error("****Manufacture BatchImport Ex :[" + e.getMessage() + "]");
        throw new GWException(e.getMessage());
    }
 }
 return i ;
 }

}
/* ExcleBalanceToDB.java
*
* Project/Product: GW
*
* Modify Information:
* =============================================================================
*   Author Date       Description
*   ---------- ---------- ---------------------------------------------------
*   Administrator  2008-2-22   First release
*
* Licensed Materials - Property of APEP
* =============================================================================
*    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
*    All Rights Reserved.
*
*    This software is the confidential and proprietary information of
*    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
*    You shall not disclose such Confidential Information and shall use it only
*    in accordance with the terms of the license agreement you entered into 
*    with Shanghai Auto Mart E-Procurement Co., Ltd.
*
* Noties:
* =============================================================================
* 
*/







